﻿sqlplus system/admin;

create tablesapce nnblog
datafile '.../nnblog.dbf'
size 100m;

create user nnblog identified by nnblog default tablespace nnblog;
grant connect to nnblog;
grant resource to nnblog;

connect nnblog/nnblog;


create table hobby(
       id number(10) primary key,
       name varchar(50),
       code number(2) unique
);
create sequence hobby_id start with 0 increment by 1 minvalue 0;
insert into hobby values(hobby_id.nextval,'学习',1);
insert into hobby values(hobby_id.nextval,'唱歌',2);
insert into hobby values(hobby_id.nextval,'看书',3);
insert into hobby values(hobby_id.nextval,'爬山',4);
insert into hobby values(hobby_id.nextval,'写代码',5);
insert into hobby values(hobby_id.nextval,'调bug',6);
commit;
select * from hobby;

create table nativePlace(
       id number(10) primary key,
       name varchar(50),
       code varchar(6) 
);
alter table NATIVEPLACE add constraint unique1 unique (CODE);


create sequence nativePlace_id start with 0 increment by 1 minvalue 0;
insert into nativePlace values(nativePlace_id.nextval,'山东','37');
insert into nativePlace values(nativePlace_id.nextval,'济南','3701');
insert into nativePlace values(nativePlace_id.nextval,'̩泰安','3709');
insert into nativePlace values(nativePlace_id.nextval,'北京','01');
insert into nativePlace values(nativePlace_id.nextval,'朝阳区','0101');
commit;
select * from nativePlace;

select * from nativePlace where length(code)=2;
select * from nativePlace where length(code)=4 and substr(code,1,2)='37';


create table users(
       id number(10) primary key,
       name varchar2(50) not null,
       nickName varchar(80) not null,	--新加入列，昵称列。
       password varchar2(50) not null,
       sex char(1) default '1',
       email varchar2(100)
);
alter table USERS add unique (NAME)

select * from users;
delete from users where id=5;
commit;

create sequence users_id start with 0 increment by 1 minvalue 0 maxvalue 99999999;

insert into users values(users_id.nextval,'admin','我是老大','admin','1','albert@qq.com');
insert into users values(users_id.nextval,'test','深情的威那斯','test','0','john@qq.com');
commit;

create table userDetail(
       id number(10) primary key,
       nativeplace_code varchar2(6) references nativePlace(code),
       hobby_code varchar2(50),
       userId number(10) references users(id)
);
alter table USERDETAIL add constraint foreign1 foreign key (NATIVEPLACE_CODE) references nativeplace (CODE);
create sequence userdetail_id start with 0 increment by 1 minvalue 0;

select * from userDetail;
delete userdetail where id=11;

insert into userdetail values(userdetail_id.nextval,'3701','1,2,3',1);
commit;

--业务代码
select * from userDetail;
select table_name from user_tables;
select * from users;

delete from users where id in(31,32);

select u.id,u.name,ud.nativeplace_code,ud.hobby_code from users u left outer join userDetail ud on u.id=ud.userId;

select u.id,u.name,u.password,u.sex,u.email,np.name,ud.hobby_code
from users u left outer join Userdetail ud on u.id=ud.userid
left outer join nativeplace np on ud.nativeplace_code=np.code
where u.id=29;

select u.id,u.name,u.password,u.sex,u.email,np.name nativePlace_name,ud.hobby_code  
from users u left outer join Userdetail ud on u.id=ud.userid 
left outer join nativeplace np on ud.nativeplace_code=np.code order by u.id where u.id=38;

--分页代码
--分页准备代码
select u.id,u.name,u.password,u.sex,u.email,np.name nativePlace_name,ud.hobby_code  
from users u left outer join Userdetail ud on u.id=ud.userid 
left outer join nativeplace np on ud.nativeplace_code=np.code order by u.id;

--核心分页代码
select * from (
  select baseTable.*,rownum as rn from (
    select u.id,u.name,u.password,u.sex,u.email,np.name nativePlace_name,ud.hobby_code  
           from users u left outer join Userdetail ud on u.id=ud.userid 
           left outer join nativeplace np on ud.nativeplace_code=np.code order by u.id) baseTable 
  where rownum<=(2)*3) 
where rn>(2-1)*3;    


--关键字查询

select count(1) from users u left outer join Userdetail ud on u.id=ud.userid
left outer join nativeplace np on ud.nativeplace_code=np.code where 1=1 
and u.name like '%a%' and u.sex=1  
order by u.id;
